IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetDelegateIdsForSupervisor]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetDelegateIdsForSupervisor]
GO

SET QUOTED_IDENTIFIER OFF
GO
/******************************************************************              
* Name:     [GetDelegateIdsForSupervisor]              
* Purpose:  Get the list of delegates for the supervisor               
* PARAMETERS(IN)              
* Name          Description                   
* -------------      -------------------------------------------              
*	@supervisorId - UserId for whom the delegates have to be seleceted
*	@sortColumn   -	Sort column(lastname,username of the user or 
		      	fully qualified name of the organization.
*	@sortOrder bit- Sort Order(Ascending or Descending) 
                 
*********************************************************************/ 
CREATE PROCEDURE dbo.[GetDelegateIdsForSupervisor]
(
	@supervisorId int,
	@sortColumn varchar(500),
	@sortOrder bit
)
AS BEGIN
	Select DelegateId as Id from COI_Delegate_Map cdm
	inner join AdmPerson adp
	on cdm.DelegateId = adp.Id
	Left join FwkDomainUser fdu
	on fdu.Id = adp.FwkDomainUserId
	LEFT JOIN AdmInternalPerson aip
	on aip.AdmPersonId = adp.Id
	Left JOIN FwkDomainOrganization fdo
	on fdo.Id = aip.PrimaryOrganizationId
	where cdm.SupervisorId = @supervisorId
	and Removed = 0
	Order by 
		CASE @sortOrder 
			WHEN 0 THEN
				CASE @sortColumn 
					WHEN 'Name' THEN adp.[Lastname]
					WHEN 'UserName' THEN fdu.[Username]
					WHEN 'Organization' THEN fdo.[FullyQualifiedName]
					ELSE adp.[Lastname]
					END
				END DESC,
		CASE @sortOrder
			WHEN 1 THEN 
				CASE @SortColumn
					WHEN 'Name' THEN adp.[Lastname]
					WHEN 'UserName' THEN fdu.[Username]
					WHEN 'Organization' THEN fdo.[FullyQualifiedName]
					ELSE adp.[Lastname]
					END
				END
			
				
END